﻿using BCCommon;
using BCCommon.TMS.FreightEnums;
using BCEntity.TMS.HorsemanFreightConfiguration;
using Dapper;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Threading.Tasks;

namespace BCData.TMS.HorsemanFreightConfiguration
{
    public class HorsemanFreightConfigurationData : IHorsemanFreightConfigurationData
    {
        private readonly IDatabaseContext databaseContext;
        private const string QUERY = @"SELECT A.HorsemanFreightConfigurationId,A.IsBase,A.CountyId,A.CountyCode,B.CountyName,B.ProvinceId,B.ProvinceCode,B.ProvinceName,B.CityId,
                                            B.CityCode,B.CityName,A.DistanceType,A.StartDistance,A.StartWeight,A.StartPrice,A.NightStartDistance,A.NightStartWeight,A.NightStartPrice,
                                            A.NightServiceFee,A.NightStartTime,A.NightEndTime 
                                        FROM HorsemanFreightConfiguration A 
                                        Left JOIN County B ON A.CountyId=B.CountyId";
        public HorsemanFreightConfigurationData(IDatabaseContext databaseContext)
        {
            this.databaseContext = databaseContext;
        }


        public async Task<bool> CheckExistsAsync(long horsemanFreightConfigurationId)
        {
            string sql = @"SELECT 1 FROM HorsemanFreightConfiguration  WHERE HorsemanFreightConfigurationId=?HorsemanFreightConfigurationId";
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?HorsemanFreightConfigurationId", horsemanFreightConfigurationId, DbType.Int64);
            return await this.databaseContext.Slave.ExecuteScalarAsync(sql, parameters) != null;
        }

        public async Task<bool> DeleteAsync(long horsemanFreightConfigurationId)
        {
            string sql = @"DELETE FROM HorsemanFreightConfiguration WHERE HorsemanFreightConfigurationId=?HorsemanFreightConfigurationId";
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?HorsemanFreightConfigurationId", horsemanFreightConfigurationId, DbType.Int64);
            return await this.databaseContext.Master.ExecuteAsync(sql, parameters, databaseContext.Transaction) > 0;
        }

        public async Task<(IEnumerable<HorsemanFreightConfigurationViewEntity>, int)> GetListAsync(bool? isBase, List<string> countyCodes, DistanceCalculateType? distanceCalculateType, bool? ascending, string sortName, int pageIndex, int pageSize)
        {
            List<string> wheres = new List<string>();
            DynamicParameters parameters = new DynamicParameters();
            if (isBase.HasValue)
            {
                wheres.Add("A.IsBase = ?IsBase");
                parameters.Add("?IsBase", isBase.Value, DbType.Boolean);
            }

            if (countyCodes.Any())
            {
                wheres.Add(" FIND_IN_SET(A.CountyCode,?CountyCodes)"); 
                parameters.Add("?CountyCodes", string.Join(',', countyCodes), DbType.AnsiString);
            }
            if (distanceCalculateType.HasValue)
            {
                wheres.Add("A.DistanceType = ?DistanceType");
                parameters.Add("?DistanceType", distanceCalculateType.Value.ToString(), DbType.AnsiString);

            }
            string sql = string.Format(@"{0} {1} ORDER BY {4} {5} LIMIT {2},{3}; SELECT COUNT(1) FROM HorsemanFreightConfiguration A  Left Join County B ON A.CountyId=B.CountyId {1}",
                QUERY,
                wheres.Count > 0 ? " WHERE " + string.Join(" AND ", wheres) : "",
                (pageIndex == 0 ? 0 : pageIndex - 1) * pageSize,
                pageSize,
                string.IsNullOrWhiteSpace(sortName) ? "HorsemanFreightConfigurationId" : sortName,
                ascending.HasValue && ascending.Value ? "ASC" : "DESC");
            return await this.databaseContext.Slave.QueryMultipleAsync<HorsemanFreightConfigurationViewEntity, int>(sql, parameters);
        }

        public async Task<HorsemanFreightConfigurationEntity> InserAsync(HorsemanFreightConfigurationEntity entity)
        {
            return await databaseContext.Master.InsertAsync(entity);
        }

        public async Task<HorsemanFreightConfigurationEntity> UpdateAsync(HorsemanFreightConfigurationEntity entity)
        {
            return await this.databaseContext.Master.UpdateAsync(entity);
        }

        public async Task<bool> CheckExistsAsync(string countyCode)
        {
            string sql = @"SELECT 1 FROM HorsemanFreightConfiguration  WHERE CountyCode=?CountyCode";
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?CountyCode", countyCode, DbType.AnsiString);
            return await this.databaseContext.Slave.ExecuteScalarAsync(sql, parameters) != null;
        }

        public async Task<IEnumerable<HorsemanFreightConfigurationEntity>> GetByCountyCodeAsync()
        {
            string sql = string.Format("{0} WHERE A.IsBase=1; ", QUERY);
            return await this.databaseContext.Slave.QueryAsync<HorsemanFreightConfigurationEntity>(sql);
        }
        public async Task<IEnumerable<HorsemanFreightConfigurationEntity>> GetByCountyCodeAsync(string countyCode)
        {
            string sql = string.Format("{0} WHERE A.CountyCode=?CountyCode; ", QUERY);
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?CountyCode", countyCode, DbType.AnsiString);
            return await this.databaseContext.Slave.QueryAsync<HorsemanFreightConfigurationEntity>(sql, parameters);
        }

        public async Task<HorsemanFreightConfigurationEntity> GetByHorsemanFreightConfigurationIdAsync(long horsemanFreightConfigurationId)
        {
            string sql = string.Format("{0} WHERE A.HorsemanFreightConfigurationId=?HorsemanFreightConfigurationId; ", QUERY);
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?HorsemanFreightConfigurationId", horsemanFreightConfigurationId, DbType.Int64);
            return await this.databaseContext.Slave.QueryFirstOrDefaultAsync<HorsemanFreightConfigurationEntity>(sql, parameters);
        }
    }
}
